package com.central.apps.controller;

import com.central.app.service.HandlerService;
import com.central.common.auth.details.LoginAppUser;
import com.central.common.exception.business.BusinessException;
import com.central.common.exception.service.ServiceException;
import com.central.common.util.SysUserUtil;
import com.central.common.web.Result;
import lombok.extern.log4j.Log4j2;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.collections.MapUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.interceptor.TransactionAspectSupport;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @Author mengweitao
 * 采购订单  电商订单，到货日期导入
 */
@Log4j2
@RestController
@RequestMapping("/po")
public class PoImportController {

    private final static String XLS = "xls";
    private final static String XLSX = "xlsx";

    @Autowired
    private HandlerService handlerService;


    /**
     * 采购订单行 电商订单号，下单日期，到货周期  根据物料编码
     * @param myfile
     * @throws Exception
     */
    @PostMapping("/poImportExcel")
    @Transactional(rollbackFor = Exception.class)
    public Result inquiryImportExcel(@RequestParam("file") MultipartFile myfile, @RequestParam("id") String id) throws Exception {
        try {
            List<Map<String, Object>> bySql = handlerService.findBySql("select ponum from gq_po where id = '" + id + "'");
            if(CollectionUtils.isEmpty(bySql)){
                return Result.failed("未根据订单id找到对应订单，订单id为:"+id);
            }
            String ponum = MapUtils.getString(bySql.get(0), "ponum");
            Workbook workbook = null;
            String fileName = myfile.getOriginalFilename();
            if (fileName.endsWith(XLS)) {//获取文件后缀
                workbook = new XSSFWorkbook(myfile.getInputStream());
            } else if (fileName.endsWith(XLSX)) {
                workbook = new XSSFWorkbook(myfile.getInputStream());
            } else {
                return Result.failed("文件不是excel文件");
            }

            //获取excel的Sheet页
            Sheet sheet1 = workbook.getSheet("Sheet1");
            //判断excel一共多少行
            int rows = sheet1.getLastRowNum();
            if (rows == 0) {
                //excel里面没数据
                return Result.failed("请填写数据");
            }

            //检验单元格格式是否正确
            for (int i = 0; i <= rows; i++) {
                int line = i + 1;
                //读取左上端单元格
                Row row = sheet1.getRow(i);
                if (row != null) {
                    //读取excel
                    String itemnum = getCellValue(row, 0).toString().trim();//物料编码
                    String itemdesc = getCellValue(row, 1).toString().trim();//物料名称
                    String commerceorder = getCellValue(row, 2).toString().trim();//电商订单号
                    String commertime = getCellValue(row, 3).toString().trim();//下单日期
                    String deliverycycle = getCellValue(row, 4).toString().trim();//到货周期
                    //判断模板标头是否正确
                    if (i == 0) {
                        if (!"物料编码".equals(itemnum) || !"物料名称".equals(itemdesc) || !"电商订单号".equals(commerceorder)
                                || !"下单日期".equals(commertime)  || !"到货周期".equals(deliverycycle)) {
                            return Result.failed("导入的模板错误，请下载模板进行导入");
                        }
                    }else{
                        if(StringUtils.isBlank(itemnum)){
                            continue;
                        }
                        //根据订单编号，物料编码，校验该订单下是否存在该修改物料
                        List<Map<String, Object>> bySql1 = handlerService.findBySql("select * from gq_posline where poid='" + id + "'and ponum = '" + ponum + "'and itemnum='" + itemnum + "'");
                        if(CollectionUtils.isEmpty(bySql1)){
                            throw new ServiceException("第" + line + "行物料编码为"+itemnum+"的物料不存在该采购订单下！");
                        }

                        Cell cell = row.getCell(3);
                        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
                        String format1 = "";
                        if (cell != null) {
                            try {
                                double numericCellValue = cell.getNumericCellValue();
                                Date date = DateUtil.getJavaDate(numericCellValue);
                                format1 = format.format(date);
                            } catch (Exception e) {
                                throw new ServiceException("第" + line + "行输入的下单日期格式不正确，应设置为单元格格式为yyyy/m/d");
                            }
                        }
                        handlerService.findBySql("update gq_posline set commerceorder = '" +commerceorder + "',commertime = '" + format1 + "',deliverycycle = '" + deliverycycle + "' where poid = '" + id + "' and ponum = '" + ponum + "' and itemnum = '" + itemnum + "'");
                    }
                }
            }
        } catch (Exception e) {
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return Result.failed(e.getMessage());
        }
        return Result.succeed("导入成功");
    }




    private boolean checkNullRow(Row row) {
        boolean rowNull = false;//不是空行
        if(row  == null){
            rowNull = true;
        }else{
            boolean isEmpty = true;
            for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
                Cell cell = row.getCell(j);
                if (cell != null && cell.getCellType() != CellType.BLANK) {
                    isEmpty = false;
                    break;
                }
            }
            if (isEmpty) {
                // 行为空
                rowNull = true;
            } else {
                // 行非空
                rowNull = false;
            }
        }
        return rowNull;
    }

    public HSSFDataValidation createBox(String col, Map<String, String> boxMap, int firstRow, int lastRow, int firstCol, int lastCol) {
        HSSFDataValidation dataValidation = null;
        //查询码值表
        String cols = "";
        if (null != boxMap.get(col)) {
            cols = boxMap.get(col);
        }
        //设置下拉框
        if (cols.length() > 0 && null != cols) {
            String str[] = cols.split(",");
            CellRangeAddressList cas = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
            //创建下拉数据列
            DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(str);
            //将下拉数据放入下拉框
            dataValidation = new HSSFDataValidation(cas, dvConstraint);
        }
        return dataValidation;
    }


    /**
     * 获取Cell内容
     */
    public Object getCellValue(Row row, int column) {
        Object val = "";
        try {
            Cell cell = row.getCell(column);
            if (cell != null) {

                if (cell.getCellTypeEnum()==CellType.NUMERIC) {
                    val = cell.getNumericCellValue();
                    DecimalFormat df = new DecimalFormat("#.#########");
                    String cellValueStr = String.valueOf(val);
                    val = df.format(Double.valueOf(cellValueStr));
                } else if (cell.getCellTypeEnum() == CellType.STRING) {
                    val = cell.getStringCellValue();
                } else if (cell.getCellTypeEnum() == CellType.FORMULA) {
                    val = cell.getCellFormula();
                } else if (cell.getCellTypeEnum() == CellType.BOOLEAN) {
                    val = cell.getBooleanCellValue();
                } else if (cell.getCellTypeEnum() == CellType.ERROR) {
                    val = cell.getErrorCellValue();
                }
            }
        } catch (Exception e) {
            return val;
        }
        return val;
    }


    /**
     * 增加批注
     * @param row
     * @param col
     * @param poiCell
     * @param poiRow
     * @param columnComment
     */
    private void setCommentsOfPoiCell(int row,int col, Cell poiCell,Row poiRow,String columnComment){
        //增加批注
        if(StringUtils.isNotBlank(columnComment) && !"null".equals(columnComment)){
            ClientAnchor anchor = new HSSFClientAnchor();
            //dx1、dy1、dx2、dy2 四个参数是坐标点
            //dx1、dy1为起始单元格的x、y坐标，dx2、dy2为结束单元格的x、y坐标
            anchor.setDx1(0);
            anchor.setDx2(0);
            anchor.setDy1(0);
            anchor.setDy2(0);
            //col1、row1、col2、row2 四个参数是编辑和显示批注时的大小
            //col1、row1为单元格的起始列、行，col2、row2为单元格的终止列、行
            anchor.setCol1(col);
            anchor.setRow1(row);
            anchor.setCol2(col);
            anchor.setRow2(row);
            Drawing draw = poiRow.getSheet().createDrawingPatriarch();
            Comment commentOfCell = draw.createCellComment(anchor);
            commentOfCell.setString(new HSSFRichTextString(columnComment));
            poiCell.setCellComment(commentOfCell);
        }
    }



}
